﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public class MyDbHelperA
{
    #region 本类里调用的几个字段对象

    //获取路径
    //private static string path=System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase+@"db\DB.mdb";
    //数据库连接字符串字段对象
    //private static string DM_ConnectionString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source="+path;

    //private static string DM_ConnectionString = @"Data Source=192.168.1.126;Database=HKERP;Uid=sa;pwd=liy1126;";
    private static string DM_ConnectionString = @"Data Source=192.168.10.90;Database=KM_RPT;Uid=sa;pwd=Km141201;";
    //private static string DM_ConnectionString = @"Data Source=192.168.1.221;Database=m1a9jxc;Uid=sa;pwd=test;";
    //private static string DM_ConnectionString = @"Data Source=192.168.10.11;Database=HKERP;Uid=RPT;pwd=Km141201;";
    //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.		
    //private static string DM_ConnectionString = ConfigurationManager.AppSettings["ConnectionString"];


    //数据库连接字段对象
    private SqlConnection DM_Connection = new SqlConnection(DM_ConnectionString);

    //命令字段对象
    private SqlCommand DM_Command = null;

    //数据适配器
    private SqlDataAdapter DM_Adapter = null;

    //数据集
    private DataSet DM_ds = null;

    //创建命令生成器
    private SqlCommandBuilder builder = null;

    //创建读取器
    private SqlDataReader DM_Reader = null;

    #endregion


    #region 几个打开关闭的方法

    /// <summary>
    /// 打开连接
    /// </summary>
    private void DM_OpenConnection()
    {
        if (DM_Connection != null)
            if (DM_Connection.State == ConnectionState.Closed)
                DM_Connection.Open();
    }

    /// <summary>
    /// 关闭读取器
    /// </summary>
    public void DM_CloseReader()
    {
        //判断读取器是否赋值过对象,如果有,将它关闭
        if (DM_Reader != null)
            if (!DM_Reader.IsClosed)
                DM_Reader.Close();
    }

    /// <summary>
    /// 关闭连接
    /// </summary>
    public void DM_CloseConnection()
    {
        if (DM_Connection != null)
            if (DM_Connection.State == ConnectionState.Open)
                DM_Connection.Close();
    }

    /// <summary>
    /// 清除命令参数
    /// </summary>
    public void DM_ClearCmdParameters()
    {
        if (DM_Command != null)
            if (DM_Command.Parameters.Count > 0)
                DM_Command.Parameters.Clear();
    }

    #endregion

    #region 关闭此方法

    /*

        #region 查询数据，返回读取器DataReader  DM_GetDataReader
        /// <summary>
        /// 查询数据，返回读取器SqlDataReader 注意：出错返回“null” 
        /// 请使用完SqlDataReader后调用本类的DM_CloseReader()方法关闭读取器
        /// </summary>
        /// <param name="CommandString">查询语句 如："select * from students"</param>
        /// <returns></returns>
        public SqlDataReader DM_GetDataReader(String CommandString)
        {
            try
            {
                //关闭连接
                DM_CloseConnection();
                //给命令参数赋值之前,先检查参数是否为空,不为空就先空参数,不清空就会发生错误
                if (DM_Command.Parameters.Count > 0)
                    DM_Command.Parameters.Clear();

                //设置数据源字符串
                DM_Command.CommandText = CommandString;
                //指定命令为Sql文本命令
                DM_Command.CommandType = CommandType.Text;
                //打开连接
                DM_OpenConnection();
                //执行
                List<SqlDataReader> reader = new List<SqlDataReader> { DM_Command.ExecuteReader() };
                //返回
                return reader[0];
            }
            catch (Exception)
            {
                //出错,返回NULL
                return null;
            }
            finally
            {
                this.DM_CloseReader();
                this.DM_CloseConnection();
            }

        }
        #endregion

        */

    #endregion

    #region 返回执行结果中首行首列的值 DM_GetExecuteScalar
    /// <summary>
    /// 返回执行结果中首行首列的值，该方法只能执行select语句，返回int类型   注意：出错返回“-1”
    /// </summary>
    /// <param name="CommandString">统计命令字符串 如："select count(*) from students "</param>
    /// <returns></returns>
    public int DM_GetExecuteScalar(String CommandString)
    {

        try
        {
            DM_Command = new SqlCommand();
            //指定连接器
            DM_Command.Connection = DM_Connection;
            //设置数据源字符串
            DM_Command.CommandText = CommandString;
            //指定命令为Sql文本命令
            DM_Command.CommandType = CommandType.Text;
            //打开连接
            DM_OpenConnection();
            //返回结果
            return ((int)DM_Command.ExecuteScalar());
        }
        catch (Exception)
        {

            //出错,返回:-1
            return -1;
        }
        finally
        {
            //释放命令内存
            DM_Command.Dispose();
            //关闭连接
            DM_CloseConnection();
        }
    }

    /// <summary>
    /// 返回执行结果中首行首列的值，该方法只能执行select语句或存储过程
    /// </summary>
    /// <param name="Command">要对sql server数据库执行的sql语句或存储过程命令对象</param>
    /// <returns>返回int类型   注意：出错返回“-1”</returns>
    public int DM_GetExecuteScalar(SqlCommand Command)
    {

        try
        {
            //指定连接器
            Command.Connection = DM_Connection;
            //打开连接
            DM_OpenConnection();
            //返回结果
            return ((int)Command.ExecuteScalar());
        }
        catch (Exception)
        {

            //出错,返回:-1
            return -1;
        }
        finally
        {
            //释放命令内存
            Command.Dispose();
            //关闭连接
            DM_CloseConnection();
        }
    }

    /// <summary>
    /// 返回执行结果中首行首列的值，该方法只能执行select语句，返回int类型   注意：出错返回“-1”
    /// </summary>
    /// <param name="CommandString">统计命令字符串 如："select count(*) from students "</param>
    /// <returns></returns>
    public object DM_GetExecuteScalarObj(String CommandString)
    {

        try
        {
            DM_Command = new SqlCommand();
            DM_Command.Connection = this.DM_Connection;
            //设置数据源字符串
            DM_Command.CommandText = CommandString;
            //指定命令为Sql文本命令
            DM_Command.CommandType = CommandType.Text;
            //打开连接
            DM_OpenConnection();
            //返回结果
            return DM_Command.ExecuteScalar();
        }
        catch (Exception)
        {

            //出错,返回:-1
            return -1;
        }
        finally
        {
            //关闭连接
            DM_CloseConnection();
            DM_Command.Dispose();
        }
    }
    #endregion

    #region 返回所影响的行数 DM_ExecuteNonQuery
    /// <summary>
    /// 返回所影响的行数，本方法执行Update、Insert、Delete操作 注：出错返回“-1”
    /// </summary>
    /// <param name="CommandString">插入、更新、删除命令字符串 如："Insert into Students(studnetName,birthday) values ('张三','2010-1-1')"</param>
    /// <returns></returns>
    public int DM_ExecuteNonQuery(String CommandString)
    {
        try
        {
            DM_Command = new SqlCommand();
            //指定连接器
            DM_Command.Connection = DM_Connection;
            //设置数据源字符串
            DM_Command.CommandText = CommandString;
            //指定命令为Sql文本命令
            DM_Command.CommandType = CommandType.Text;
            //打开连接
            DM_OpenConnection();
            //返回结果
            return DM_Command.ExecuteNonQuery();
        }
        catch (Exception)
        {
            //出错,返回:-1
            return -1;
        }
        finally
        {
            //释放命令内存
            DM_Command.Dispose();
            //关闭连接
            DM_CloseConnection();
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="CommandString">插入、更新、删除命令字符串 如："Insert into Students(studnetName,birthday) values ('张三','2010-1-1')"</param>
    /// <returns></returns>

    /// <summary>
    /// 返回所影响的行数，本方法执行Update、Insert、Delete操作 注：出错返回“-1”
    /// </summary>
    /// <param name="Command">Command执行命令，里面可包含参数。</param>
    /// <returns></returns>
    public int DM_ExecuteNonQuery(SqlCommand Command)
    {
        try
        {
            Command.CommandType = CommandType.Text;
            //给命令指定连接对象
            Command.Connection = DM_Connection;
            //打开连接
            DM_OpenConnection();
            //返回结果
            return Command.ExecuteNonQuery();

        }
        catch (Exception)
        {
            //出错,返回:-1
            return -1;
        }
        finally
        {
            Command.Dispose();
            //关闭连接
            DM_CloseConnection();
        }
    }
    #endregion

    #region 获取查询数据到数据集

    /// <summary>
    /// 查询数据到数据集    注意：出错返回“null”
    /// </summary>
    /// <param name="CommandString">查询命令 如："select * from students"</param>
    /// <returns></returns>
    public DataSet DM_GetDateSet(String CommandString)
    {
        try
        {
            DM_Adapter = new SqlDataAdapter(CommandString, DM_Connection);
            DM_Adapter.SelectCommand.CommandTimeout = 250;
            DM_ds = new DataSet();
            DM_Adapter.Fill(DM_ds); //填充数据到数据集
            return DM_ds;   //返回数据集
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            //释放资源
            DM_Adapter.Dispose();
            DM_ds.Dispose();
        }
    }

    /// <summary>
    /// 查询数据到数据集    注意：出错返回“null”
    /// </summary>
    /// <param name="CommandString">查询命令 如："select * from students"</param>
    /// <param name="TableName">DataSet表名</param>
    /// <returns></returns>
    public DataSet DM_GetDateSet(String CommandString, string TableName)
    {
        try
        {
            DM_Adapter = new SqlDataAdapter(CommandString, DM_Connection);
            DM_ds = new DataSet();
            DM_Adapter.Fill(DM_ds, TableName); //填充数据到数据集
            return DM_ds;   //返回数据集
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            //释放资源
            DM_Adapter.Dispose();
            DM_ds.Dispose();
        }
    }

    /// <summary>
    /// 查询数据到数据集    注意：出错返回“null”
    /// </summary>
    /// <param name="CommandString">查询命令 如："select * from students"</param>
    /// <returns></returns>
    public DataSet DM_GetDateSet(SqlCommand command)
    {
        try
        {
            command.CommandType = CommandType.Text;
            command.Connection = this.DM_Connection;
            DM_Adapter = new SqlDataAdapter(command);
            DM_ds = new DataSet();
            DM_Adapter.Fill(DM_ds); //填充数据到数据集
            return DM_ds;   //返回数据集
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {
            //释放资源
            DM_Adapter.Dispose();
            DM_ds.Dispose();
        }
    }
    #endregion

    #region   由DataSet更新数据库里面数据的方法
    /// <summary>
    /// 由DataSet更新数据库里面的数据 注：返回bool类型，操作成功返回“true”，相反，失败为“false”
    /// </summary>
    /// <param name="CommandString">获取数据到数据集时所用的select命令 如：填充数据到数据集时使用的select语句是："select * from students"</param>
    /// <param name="ds">更改过的DataSet</param>
    /// <returns></returns>
    public bool DM_UpdateDateSet(String CommandString, DataSet ds)
    {

        try
        {
            DM_Adapter = new SqlDataAdapter(CommandString, DM_Connection);
            builder = new SqlCommandBuilder(DM_Adapter);
            DM_Adapter.Update(ds);
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            //释放资源
            builder.Dispose();
            DM_Adapter.Dispose();
        }
    }

    #endregion

    #region 执行Select语句的存储过程 DM_RunSelectPramGetDataSet
    /// <summary>
    /// 运行Select语句的存储过程 注意：出错返回“null”
    /// </summary>
    /// <param name="StoredProcedureName">存储过程名称</param>
    /// <returns></returns>
    public DataSet DM_RunSelectPramGetDataSet(String StoredProcedureName)
    {
        DM_Adapter = new SqlDataAdapter();
        DM_ds = new DataSet();
        DM_Command = new SqlCommand();
        try
        {
            DM_Command.Connection = DM_Connection;
            //设置数据源字符串
            DM_Command.CommandText = StoredProcedureName;
            //说明Command的类型为存储过程
            DM_Command.CommandType = CommandType.StoredProcedure;
            //给适配器的SelectCommand赋值
            DM_Adapter.SelectCommand = DM_Command;
            DM_Adapter.Fill(DM_ds);
            return DM_ds;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {

            //释放资源
            DM_Adapter.Dispose();
            DM_ds.Dispose();
            DM_Command.Dispose();
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="StoredProcedureName">存储过程名称</param>
    /// <param name="Parameters">SqlParameter数组，传引用类型</param>
    /// <returns></returns>
    public DataSet DM_RunSelectPramGetDataSet(String StoredProcedureName, ref  SqlParameter[] Parameters)
    {
        try
        {
            DM_Adapter = new SqlDataAdapter();
            DM_ds = new DataSet();
            DM_Command = new SqlCommand();
            DM_Command.Connection = DM_Connection;
            //设置数据源字符串
            DM_Command.CommandText = StoredProcedureName;
            //说明Command的类型为存储过程
            DM_Command.CommandType = CommandType.StoredProcedure;

            //判断Parameter不为空就给命令对象赋值
            if (Parameters != null && Parameters.Length > 0)
            {
                DM_Command.Parameters.AddRange(Parameters);
            }
            DM_Adapter.SelectCommand = DM_Command;
            DM_Adapter.Fill(DM_ds);
            return DM_ds;
        }
        catch (Exception)
        {
            return null;
        }
        finally
        {

            //释放资源
            DM_Adapter.Dispose();
            DM_ds.Dispose();
            DM_Command.Dispose();
        }
    }
    #endregion

    #region 执行更新、删除、插入的带参数存储过程
    /// <summary>
    /// 执行更新、删除、插入的带参数存储过程
    /// </summary>
    /// <param name="StoredProcedureName">存储过程名称</param>
    /// <param name="Parameters">SqlParameter类型的数组,传引用类型</param>
    /// <returns></returns>
    public bool DM_RunPramNonQuery(String StoredProcedureName, ref SqlParameter[] Parameters)
    {
        DM_Command = new SqlCommand();
        try
        {
            DM_Command.CommandText = StoredProcedureName;
            DM_Command.Connection = DM_Connection;
            //说明command的类型为存储过程
            DM_Command.CommandType = CommandType.StoredProcedure;
            //打开连接
            DM_OpenConnection();
            //判断Parameter不为空就给命令对象赋值
            if (Parameters != null && Parameters.Length > 0)
            {
                DM_Command.Parameters.AddRange(Parameters);
            }
            DM_Command.ExecuteNonQuery();
            return true;
        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            //关闭连接
            DM_CloseConnection();
            DM_Command.Dispose();
        }
    }
    #endregion

    #region 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">批量执行语句数组</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(string[] commandText)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    //创建命令对象
                    using (this.DM_Command = new SqlCommand())
                    {
                        this.DM_Command.Connection = this.DM_Connection;
                        this.DM_Command.CommandType = CommandType.Text;
                        this.DM_Command.Transaction = tran;
                        //开始遍历批量执行的语句 
                        foreach (string TSql in commandText)
                        {
                            this.DM_Command.CommandText = TSql;
                            this.DM_Command.ExecuteNonQuery();
                        }
                        //执行语句正确，提交事务
                        tran.Commit();
                        return true;
                    }
                }
                catch (Exception)
                {
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }

    }

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">批量执行语句数组</param>
    /// <param name="ErrorMessage">错误信息，注意：此参数为字符串引用类型。</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(string[] commandText, ref string ErrorMessage)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    //创建命令对象
                    using (this.DM_Command = new SqlCommand())
                    {
                        this.DM_Command.Connection = this.DM_Connection;
                        this.DM_Command.CommandType = CommandType.Text;
                        this.DM_Command.Transaction = tran;
                        //开始遍历批量执行的语句 
                        foreach (string TSql in commandText)
                        {
                            this.DM_Command.CommandText = TSql;
                            this.DM_Command.ExecuteNonQuery();
                        }
                        //执行语句正确，提交事务
                        tran.Commit();
                        return true;
                    }
                }
                catch (Exception exc2)
                {
                    ErrorMessage = exc2.Message;
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception exc)
        {
            ErrorMessage = exc.Message;
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }
    }

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">批量执行语句数组字符串，使用“;”分隔语句。</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(string commandText)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    //创建命令对象
                    using (this.DM_Command = new SqlCommand())
                    {
                        this.DM_Command.Connection = this.DM_Connection;
                        this.DM_Command.CommandType = CommandType.Text;
                        this.DM_Command.Transaction = tran;
                        commandText.Trim(';');
                        //开始遍历批量执行的语句 
                        foreach (string TSql in commandText.Trim(';').Split(';'))
                        {
                            this.DM_Command.CommandText = TSql;
                            this.DM_Command.ExecuteNonQuery();
                        }
                        //执行语句正确，提交事务
                        tran.Commit();
                        return true;
                    }
                }
                catch (Exception)
                {
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }

    }

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">批量执行语句数组字符串，使用“;”分隔语句。</param>
    /// <param name="ErrorMessage">错误信息，注意：此参数为字符串引用类型。</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(string commandText, ref string ErrorMessage)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    //创建命令对象
                    using (this.DM_Command = new SqlCommand())
                    {
                        this.DM_Command.Connection = this.DM_Connection;
                        this.DM_Command.CommandType = CommandType.Text;
                        this.DM_Command.Transaction = tran;
                        //开始遍历批量执行的语句 
                        foreach (string TSql in commandText.Trim(';').Split(';'))
                        {
                            this.DM_Command.CommandText = TSql;
                            this.DM_Command.ExecuteNonQuery();
                        }
                        //执行语句正确，提交事务
                        tran.Commit();
                        return true;
                    }
                }
                catch (Exception exc2)
                {
                    ErrorMessage = exc2.Message;
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception exc)
        {
            ErrorMessage = exc.Message;
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }
    }

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">参数命令数组</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(List<SqlCommand> CommandArray)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    foreach (SqlCommand command in CommandArray)
                    {
                        command.CommandType = CommandType.Text;
                        command.Connection = this.DM_Connection;
                        command.Transaction = tran;
                        command.ExecuteNonQuery();

                        //释放资源
                        command.Dispose();
                    }

                    //执行语句正确，提交事务
                    tran.Commit();
                    return true;
                }
                catch (Exception)
                {
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception)
        {
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }

    }

    /// <summary>
    /// 事务批量执行更新、删除、插入语句，全部成功返回：true，其他情况返回false
    /// </summary>
    /// <param name="commandText">参数命令数组</param>
    /// <param name="ErrorMessage">错误信息，注意：此参数为字符串引用类型。</param>
    /// <returns></returns>
    public bool ExecuteNonQueryTran(List<SqlCommand> CommandArray, ref string ErrorMessage)
    {
        try
        {
            this.DM_OpenConnection();
            using (SqlTransaction tran = this.DM_Connection.BeginTransaction())
            {
                try
                {
                    foreach (SqlCommand command in CommandArray)
                    {
                        command.CommandType = CommandType.Text;
                        command.Connection = this.DM_Connection;
                        command.Transaction = tran;
                        command.ExecuteNonQuery();

                        //释放资源
                        command.Dispose();
                    }

                    //执行语句正确，提交事务
                    tran.Commit();
                    return true;
                }
                catch (Exception exc2)
                {
                    ErrorMessage = exc2.Message;
                    //其中一条语句执行失败，回滚
                    tran.Rollback();
                    return false;
                }
            }

        }
        catch (Exception exc)
        {
            ErrorMessage = exc.Message;
            return false;
        }
        finally
        {
            this.DM_CloseConnection();
        }

    }

    #endregion


}